package com.kaizeli.website.service.impl;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.read.listener.ReadListener;
import com.kaizeli.website.pojo.dto.EmployeeResignedExcelDTO;
import com.kaizeli.website.service.EmployeeResignedImportService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.*;

/**
 * 离职员工导入服务实现类
 *
 * @author Kaizeli
 * @date 2024-10-21
 */
@Slf4j
@Service
public class EmployeeResignedImportServiceImpl implements EmployeeResignedImportService {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    @Transactional(rollbackFor = Exception.class)
    public Map<String, Object> importEmployeeResignedExcel(MultipartFile file, boolean updateIfExists) {
        Map<String, Object> result = new HashMap<>();

        if (file == null || file.isEmpty()) {
            result.put("success", false);
            result.put("message", "文件为空，请上传Excel文件");
            return result;
        }

        String filename = file.getOriginalFilename();
        if (filename == null || (!filename.endsWith(".xlsx") && !filename.endsWith(".xls"))) {
            result.put("success", false);
            result.put("message", "文件格式错误，仅支持Excel文件（.xlsx或.xls）");
            return result;
        }

        final int[] successCount = {0};
        final int[] updateCount = {0};
        final int[] failCount = {0};
        final List<String> errorMessages = new ArrayList<>();
        final Set<String> existingIdCards = new HashSet<>();

        log.info("开始导入离职员工Excel，更新模式：{}", updateIfExists ? "存在则更新" : "存在则跳过");

        try (InputStream inputStream = file.getInputStream()) {
            EasyExcel.read(inputStream, EmployeeResignedExcelDTO.class, new ReadListener<EmployeeResignedExcelDTO>() {

                @Override
                public void invoke(EmployeeResignedExcelDTO data, AnalysisContext context) {
                    Integer rowNum = context.readRowHolder().getRowIndex() + 1;

                    try {
                        String validationError = validateData(data, rowNum);
                        if (validationError != null) {
                            errorMessages.add(validationError);
                            failCount[0]++;
                            return;
                        }

                        if (existingIdCards.contains(data.getIdCard())) {
                            errorMessages.add(String.format("第%d行：身份证号 [%s] 在Excel中重复", rowNum, data.getIdCard()));
                            failCount[0]++;
                            return;
                        }

                        String checkSql = "SELECT COUNT(*) FROM employee_resigned WHERE id_card = ?";
                        Integer count = jdbcTemplate.queryForObject(checkSql, Integer.class, data.getIdCard());
                        boolean exists = (count != null && count > 0);

                        if (exists) {
                            if (updateIfExists) {
                                String updateSql = "UPDATE employee_resigned SET " +
                                        "archive_no = ?, project_code = ?, client_company = ?, department = ?, team = ?, " +
                                        "position = ?, employee_type = ?, name = ?, gender = ?, marital_status = ?, " +
                                        "native_place = ?, nationality = ?, political_status = ?, birthday = ?, " +
                                        "registered_address = ?, current_address = ?, phone = ?, email = ?, " +
                                        "hire_date = ?, regular_date = ?, education_degree = ?, major = ?, " +
                                        "graduate_school = ?, graduate_date = ?, skill_certificates = ?, base_salary = ?, " +
                                        "emergency_contact_name = ?, emergency_contact_phone = ?, bank_card_no = ?, bank_name = ?, " +
                                        "is_contract_renewed = ?, contract_renewal_count = ?, latest_contract_start_date = ?, " +
                                        "latest_contract_end_date = ?, latest_renewal_contract_period = ?, latest_contract_company = ?, " +
                                        "contract_history_period = ?, contract_history_company = ?, remarks = ?, " +
                                        "resignation_date = ?, resignation_reason = ?, is_resignation_processed = ? " +
                                        "WHERE id_card = ?";

                                jdbcTemplate.update(updateSql,
                                        data.getArchiveNo(), data.getProjectCode(), data.getClientCompany(), data.getDepartment(), data.getTeam(),
                                        data.getPosition(), data.getEmployeeType(), data.getName(), data.getGender(), data.getMaritalStatus(),
                                        data.getNativePlace(), data.getNationality(), data.getPoliticalStatus(), data.getBirthday(),
                                        data.getRegisteredAddress(), data.getCurrentAddress(), data.getPhone(), data.getEmail(),
                                        data.getHireDate(), data.getRegularDate(), data.getEducationDegree(), data.getMajor(),
                                        data.getGraduateSchool(), data.getGraduateDate(), data.getSkillCertificates(), data.getBaseSalary(),
                                        data.getEmergencyContactName(), data.getEmergencyContactPhone(), data.getBankCardNo(), data.getBankName(),
                                        data.getIsContractRenewed(), data.getContractRenewalCount(), data.getLatestContractStartDate(),
                                        data.getLatestContractEndDate(), data.getLatestRenewalContractPeriod(), data.getLatestContractCompany(),
                                        data.getContractHistoryPeriod(), data.getContractHistoryCompany(), data.getRemarks(),
                                        data.getResignationDate(), data.getResignationReason(), data.getIsResignationProcessed(),
                                        data.getIdCard()
                                );

                                updateCount[0]++;
                                log.info("成功更新离职员工：{} - {}", data.getIdCard(), data.getName());
                            } else {
                                errorMessages.add(String.format("第%d行：身份证号 [%s] 已存在于数据库中（跳过）",
                                        rowNum, data.getIdCard()));
                                failCount[0]++;
                                return;
                            }
                        } else {
                            String insertSql = "INSERT INTO employee_resigned (archive_no, project_code, client_company, department, team, " +
                                    "position, employee_type, name, gender, marital_status, native_place, nationality, political_status, " +
                                    "id_card, birthday, registered_address, current_address, phone, email, hire_date, regular_date, " +
                                    "education_degree, major, graduate_school, graduate_date, skill_certificates, base_salary, " +
                                    "emergency_contact_name, emergency_contact_phone, bank_card_no, bank_name, is_contract_renewed, " +
                                    "contract_renewal_count, latest_contract_start_date, latest_contract_end_date, " +
                                    "latest_renewal_contract_period, latest_contract_company, contract_history_period, " +
                                    "contract_history_company, remarks, resignation_date, resignation_reason, is_resignation_processed) " +
                                    "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

                            jdbcTemplate.update(insertSql,
                                    data.getArchiveNo(), data.getProjectCode(), data.getClientCompany(), data.getDepartment(), data.getTeam(),
                                    data.getPosition(), data.getEmployeeType(), data.getName(), data.getGender(), data.getMaritalStatus(),
                                    data.getNativePlace(), data.getNationality(), data.getPoliticalStatus(), data.getIdCard(), data.getBirthday(),
                                    data.getRegisteredAddress(), data.getCurrentAddress(), data.getPhone(), data.getEmail(),
                                    data.getHireDate(), data.getRegularDate(), data.getEducationDegree(), data.getMajor(),
                                    data.getGraduateSchool(), data.getGraduateDate(), data.getSkillCertificates(), data.getBaseSalary(),
                                    data.getEmergencyContactName(), data.getEmergencyContactPhone(), data.getBankCardNo(), data.getBankName(),
                                    data.getIsContractRenewed(), data.getContractRenewalCount(), data.getLatestContractStartDate(),
                                    data.getLatestContractEndDate(), data.getLatestRenewalContractPeriod(), data.getLatestContractCompany(),
                                    data.getContractHistoryPeriod(), data.getContractHistoryCompany(), data.getRemarks(),
                                    data.getResignationDate(), data.getResignationReason(), data.getIsResignationProcessed()
                            );

                            successCount[0]++;
                            log.info("成功新增离职员工：{} - {}", data.getIdCard(), data.getName());
                        }

                        existingIdCards.add(data.getIdCard());

                    } catch (Exception e) {
                        log.error("导入第{}行数据失败：{}", rowNum, e.getMessage(), e);
                        errorMessages.add(String.format("第%d行：导入失败 - %s", rowNum, e.getMessage()));
                        failCount[0]++;
                    }
                }

                @Override
                public void doAfterAllAnalysed(AnalysisContext context) {
                    log.info("离职员工Excel读取完成，新增：{}条，更新：{}条，失败：{}条",
                            successCount[0], updateCount[0], failCount[0]);
                }
            }).sheet().doRead();

            int totalSuccess = successCount[0] + updateCount[0];
            String message = updateIfExists ?
                    String.format("导入完成！新增：%d条，更新：%d条，失败：%d条", successCount[0], updateCount[0], failCount[0]) :
                    String.format("导入完成！成功：%d条，失败：%d条", successCount[0], failCount[0]);

            result.put("success", true);
            result.put("message", message);
            result.put("successCount", successCount[0]);
            result.put("updateCount", updateCount[0]);
            result.put("totalSuccess", totalSuccess);
            result.put("failCount", failCount[0]);
            result.put("errors", errorMessages);

        } catch (IOException e) {
            log.error("读取Excel文件失败", e);
            result.put("success", false);
            result.put("message", "读取Excel文件失败：" + e.getMessage());
        } catch (Exception e) {
            log.error("导入Excel文件失败", e);
            result.put("success", false);
            result.put("message", "导入失败：" + e.getMessage());
        }

        return result;
    }

    private String validateData(EmployeeResignedExcelDTO data, Integer rowNum) {
        if (data.getName() == null || data.getName().trim().isEmpty()) {
            return String.format("第%d行：姓名不能为空", rowNum);
        }

        if (data.getIdCard() == null || data.getIdCard().trim().isEmpty()) {
            return String.format("第%d行：身份证号不能为空", rowNum);
        }
        if (!data.getIdCard().matches("^\\d{17}[0-9Xx]$")) {
            return String.format("第%d行：身份证号格式不正确", rowNum);
        }

        return null;
    }

    @Override
    public byte[] downloadTemplate() {
        ByteArrayOutputStream outputStream = null;
        try {
            outputStream = new ByteArrayOutputStream();

            List<EmployeeResignedExcelDTO> templateData = new ArrayList<>();

            EmployeeResignedExcelDTO example = new EmployeeResignedExcelDTO();
            example.setArchiveNo("A001");
            example.setProjectCode("PROJ-2024-001");
            example.setName("李四");
            example.setGender("男");
            example.setIdCard("110101199001011235");
            example.setPhone("13800138001");
            example.setEmail("lisi@example.com");
            example.setHireDate(new Date());
            example.setResignationDate(new Date());
            example.setResignationReason("个人原因");
            example.setIsResignationProcessed(1);
            templateData.add(example);

            EasyExcel.write(outputStream, EmployeeResignedExcelDTO.class)
                    .sheet("离职员工导入模板")
                    .doWrite(templateData);

            return outputStream.toByteArray();

        } catch (Exception e) {
            log.error("生成模板文件失败", e);
            throw new RuntimeException("生成模板文件失败：" + e.getMessage(), e);
        } finally {
            if (outputStream != null) {
                try {
                    outputStream.close();
                } catch (IOException e) {
                    log.error("关闭输出流失败", e);
                }
            }
        }
    }
}

